SESSION
1
Total Time: 112
Minutes
Section A: Introduction
Section B: Normalization
- Basic Concept
- Redundancy
- Design Language
- Entity
- First Normal Form
- Primary Keys
- Second Normal Form
- Third Normal Form
- Relationships
- Business Rules
- Normalization Benefits
Section C: Creating a
Database
- Overview
- Database Objects
- Database Creation Process
- Transaction Log
- Create Option
- Create Syntax
- Query Analyzer
- Collate Order
- Enterprise Manager
- Recovery Considerations
Section D: Placing Database
Files
- Storage Management
- Hardware RAID
- Filegroups
- Files & Filegroups
- Filegroups Maintenance
- Performance Considerations
Section E: Maintaining
Databases
- Growth
- Alter Database
- Database Options
- Shrink Database
- Exam Database
- Space Estimation
- Transaction Log Size
- Rules of Thumb
- Log File Issues
- Extensive Log Activity
Section F: Basic SQL Data
Types
- Create Table
- Standard Data Types
- Exact Numerics
- Integer Variables
- Approximate Numerics
- Character
- Variable vs. Fixed
- Unicode
SESSION
2
Total Time: 111
Minutes
Section A: Advanced SQL Data
Types
- Date & Time
- Date Format
- Money
- Invalid Entry
- Binary
- GUID
- BLOB
- Special Data Types
Section B: Creating a Table
- Simply Syntax
- Null or Not Null
- Space Allocation
- Temporary Tables
- Primary Key Values
- Identity Column
- Using Identity Values
- Scope Identity
- Unique Identifier
- Newid Function
- Extended Properties
- Altering & Dropping
Tables
Section C: Data Integrity
Concepts
- Types
- Data Integrity
- Entity Integrity
- Domain Integrity
- Referential Integrity
Section D: Data Integrity
Implementation
- Enforcement
- Constraints
- Generic Constraints
- Defaults
- Check Constraints
- Table vs. Column Constraint
- Column Constraint
- Alter Constraints
- Constraint Options
Section E: Primary Key
Constraint
- Overview
- Implement Primary Key
Constraint
- Duplicate Key Values
- Multi-Column Key
- Unique Constraint
- Add Unique Constraint
Section F: Foreign Key
Constraint
- Overview
- Foreign Key Rules
- Cascade Update
SESSION
3
Total Time: 107
Minutes
Section A: Select Statement
- Basic Select
- Special Identifiers
- 4-Part Naming Convention
- Select Order
- Where Clauses
- Comparison Operators
- Range of Values
- Values in a List
- String Pattern Matching
- String Comparison Operator
- Like Operator
- Not Like Operator
- Null Checking
- Logical Operator
- Not, And, Or Operators
- Dynamic SQL
Section B: Formatting Result
Sets
- Sorting
- Order By
- Eliminating Duplicates
- Order By with Distinct
- Column Alias
- Using Literals
Section C: Summarizing Data
- Aggregate Functions
- Aggregate Examples
- Count Distinct
- Group By
- Group By Having
- Having with Aggregates
- Rollups
- Cube
Section D: Functions &
Set Options
- System Functions
- Set Options
- Scalar Functions
- Object Properties
- Convert Function
- String Functions
- GetDate Function
- Date & Time Functions
- Row Count Set Option
Section E: Inner Joins
- Joins
- GUI Joins
- Inner Join
- Alias Table Names
- Join Result Options
- Multiple Table Joins
SESSION
4
Total Time: 104 Minutes
Section A: Outer Joins,
Cross Joins & Unions
- Outer Joins
- Left & Right Outer Join
- Cross Join
- Self-Referencing Table
- Self-Reference Outer Join
- Denormalizing
- Unions
Section B: Subqueries
- Introduction
- Rules
- Nested Subquery
- Multiple Value Nested
Subqueries
- Distinct Subqueries
- Correlated Subqueries
- Complicated Subqueries
Section C:
Insert/Delete/Update
- Insert Statement
- Defaults
- Insert Select
- Truncate Table
- Deleting Records
- Updating Records
- Update with Join
Section D: Indexing
- Introduction
- Heap vs. Clustered
- Data Access
- Clustered Index
- Why Clustered Indexes?
- Non-Clustered Indexes
- Why Non-Clustered Indexes?
Section E: Indexes &
Fillfactor
- Creating Indexes
- Computed Columns
- Maintenance Issues
- Full Pages
- Fillfactor
- Execution Plan with Index
- Aggregate with Index
- Clustered Index
Section F: Indexes &
Fragmentation
- External Fragmentation
- Data Fragmentation
- Scan Density
- DBCC Index Defrag
- Dropping Index
- Index Hints
SESSION
5
Total Time: 105 Minutes
Section A: Index Statistics
- Overview
- Density
- Index Statistics
- Index Statistics Usage
- Updating Statistics
- View Statistics
- Set Statistic Properties
- Using Query Plan
- Update Statistics
- Force Index Usage
- Use Updated Statistics
- Update Statistic Commands
- Show Sysindexes
- Turn On Statistic
Section B: Query
Optimization
- Slow Queries
- Query Plan
- Dual Indexes Usage
- And Operators
- Or Operators
- Aggregates
- Joins
- Merge Join
- Entity Relationship Diagram
- Verify Cost
- Force Index
- Search Arguments
- Like Clause
Section C: SQL Profiler
- Overview
- Event Classes
- Run Profiler
- Tracefile Properties
- Index Tuning Wizard
- Analysis
- Trace Replay
- Access Query Analyzer
- Overview
- Set Processor Usage
- Query Governor
Section D: Views
- Overview
- View Details
- Create View
- Using Views
- Updateable Views
- Alter View
- Insert Record
- Check Options
- Update & Delete Records
Section E: Linked Servers
- Overview
- Query Types
- Setting up the Link
- Login
- Options
- Open Query
- RPC’s
- Create Linked Server (Excel)
- Distributed Passthrough
Queries
- RPC/Adhoc
- Union Query
SESSION
6
Total Time: 110
Minutes
Section A: Indexed &
Distributed Views
- Indexed View Usage
- Scheme Bound View
- Indexed View
- Data Partitioning
- Partition View
- Create Data Partition
- Create Data Partition View
- Setting Up
- Modify Users View
- Insert, Update & Delete
- View Issues
Section B: Programming SQL
- Global Functions
- Local Variables
- Define a Local Variable
- Define Multi-Local Variables
- Assignment Select Statement
- Variable Value Assignment
- Control of Flow
- Multiple Statement
- While Loop
- Until/Break
- GoTo/WaitFor
Section C: Other Language
Elements
- Simple Case Statements
- Searched Case
- Raiserror
- View Logs
- User Messages
- Validate Data
- XPLOG Events
- Commenting
Section D: Transactions
- Overview
- Rollback
- Transaction Examples
- Partial Rollback
- Nested Transactions
- Avoid Nested
- Implicit
- Implicit Starters
- Error Checking
- Set Implicit On
- Restricted & Best
Practices
- Linked Server & Remote
Procedure
- Distributed Transaction
Section E: Locks & Lock
Types
- Lost & Erroneous
Transactions
- Non-Repeatable &
Phantoms
- Benefits & Usage
- Lock Types
- Lock Process
- Intent Locks & Bulk
Update
- Isolation Levels
- Locked Record Trace
- Set Lock Time Out
SESSION
7
Total Time: 107
Minutes
Section A: Lock Manipulation
- Lock Hints
- Using Locks
- Deadlock Error
- Avoiding & Handling
Deadlocks
- Locking Best Practices
Section B: Cursors
- Overview
- Cursor Types & Behavior
- Cursor Steps
- Modifying Data
- Using Cursor
- Update Data
- Cursor Best Practices
Section C: Stored Procedures
- Overview
- Create & Execute
- Using Stored Procedures
- Parameters
- Using Parameters
- Local Variables
- Default Parameter Values
- Output Parameters
- Error Checking
- Testing Stored Procedures
- Return Status
- Plan Sharing & Best
Practices
Section D: User Defined
Functions
- Function Types
- Calling Functions
- Schema Binding
- Determinism
- Function Syntax
- Calling Scalar Functions
- Using Functions
- Table Value Functions
- Calling Table Functions
- Multi-Statement Table
- Best Practices
Section E: After Triggers
- Overview
- Plan Trigger Use
- Trigger Functionality
- Inserted, Updated, Deleted
Tables
- Using Triggers
- Testing Triggers
- Cascading Updates
SESSION
8
Total Time: 98
Minutes
Section A: Instead-Of
Triggers
- Overview
- Using Instead-Of Triggers
- Create Trigger
- Nested Triggers
- Instead-Of vs. After
Section B: Data Movement
- Overview
- DTS Tools
- DTS Packages
- DTS Data Lineage
- DTS Import/Export Wizard
- Export to Flat File
- Import Data
- DTS Package Workflow
Section C: Replication
- Overview
- Replication Roles
- Replication Types
- Merge
- Create Merge Publication
- Subscriptions
- Updating Subscribers
- New Replication Features
Section D: Permissions &
Roles
- Overview
- Object Permissions
- Chain of Ownership
- Permissions & Chain of
Ownership
- Row Level Security
- Assign Row Level Security
- Roles
- Understanding Roles
- Application Roles
Section E: XML
- Selecting
- For XML Auto
- For XML Raw
- For XML Explicit
- Open XML
- Additional Resources
|